﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

using Expo.Training.DataAccess.Activity;
using Expo.Training.BRule;

using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;

public partial class Activity_RegistrationManagement : PageBase
{
    const string ActivityPageIndex = "Activity_RegistrationManagement";

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Bind();
        }
    }

    private void Bind()
    {
        int totalCount = 0;
        this.rpt_RegistrationList.DataSource = ActivityModule.GetRegistrationsByPage(this.cc_ExPager.PageSize, this.cc_ExPager.PageIndex + 1, string.Empty, string.Empty, out totalCount);
        this.rpt_RegistrationList.DataBind();
        this.cc_ExPager.TotalRecords = totalCount;
    }

    protected void PageIndex_Changed(object sender, EventArgs e)
    {
        int pageindex = this.cc_ExPager.PageIndex;
        CookieManager.WriteCookie(ActivityPageIndex, pageindex.ToString(), DateTime.Now.AddDays(1));
        Bind();
    }

    protected void btn_Search_Click(object sender, EventArgs e)
    {
        string _title = this.txtActivityTitle.Text.Trim();
        string _realName = this.txtRealName.Text.Trim();
        string _intention = this.txtIntention.Text.Trim();
        string _condition = "1=1";

        if (!string.IsNullOrEmpty(_title))
        {
            _condition += " AND ActivityTitle LIKE '%" + _title + "%'";
        }

        if (!string.IsNullOrEmpty(_realName))
        {
            _condition += " AND RealName LIKE '%" + _realName + "%'";
        }

        if (!string.IsNullOrEmpty(_intention))
        {
            _condition += " AND Intention LIKE '%" + _intention + "%'";
        }

        int totalCount = 0;
        this.rpt_RegistrationList.DataSource = ActivityModule.GetRegistrationsByPage(this.cc_ExPager.PageSize, this.cc_ExPager.PageIndex + 1, _condition, string.Empty, out totalCount);
        this.rpt_RegistrationList.DataBind();
        this.cc_ExPager.TotalRecords = totalCount;

        this.txtQueryExpression.Value = _condition;

        
    }

    #region 导出按钮单击事件

    protected void btn_Export_Click(object sender, EventArgs e)
    {
        

        DataTable dtSource = ActivityModule.GetRegistrationsTable(this.txtQueryExpression.Value);

        ListColumnsName = new SortedList();
        ListColumnsName.Add("001-RealName", "姓名");
        ListColumnsName.Add("002-Comments", "性别");
        ListColumnsName.Add("003-Age", "年龄");
        ListColumnsName.Add("004-FamilyAddress", "家庭地址");
        ListColumnsName.Add("005-ContactAddress", "联系地址");
        ListColumnsName.Add("006-Mobile", "手机号");
        ListColumnsName.Add("007-BeginDate", "养游开始");
        ListColumnsName.Add("008-EndDate", "养游结束");
        ListColumnsName.Add("009-Intention", "意向地点");
        ListColumnsName.Add("010-Requirement", "服务需求");
        ListColumnsName.Add("011-RegNumber", "随行人数");

        Response.Clear();
        Response.BufferOutput = false;
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        var _agent = Request.Browser.Browser;
        string filename = "活动报名-" + DateTime.Now.ToShortDateString() + "_" + DateTime.Now.ToShortTimeString();
        if (_agent != "Firefox")
        {
            filename = HttpUtility.UrlEncode(filename);
        }

        Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
        Response.ContentType = "application/ms-excel";
        ExportExcel(dtSource, Response.OutputStream);
        Response.Close();
    }
    #endregion

    protected void rpt_Registration_ItemCommand(object sender, RepeaterCommandEventArgs e)
    {
        if (e.CommandName == "delete")
        {
            Guid _id = new Guid(e.CommandArgument.ToString());

            try
            {
                
                if (ActivityModule.DeleteRegistration(_id))
                {
                    this.AddEndScript("delete succes", "alert('删除活动成功！');location.href='RegistrationManagement.aspx'");
                }
                else
                {
                    this.AddEndScript("error", "alert('删除操作出错，请重试！');");
                }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }

            Bind();
        }
    }

    #region 导出Excel相关方法
    /// <summary>
    /// 导出列名
    /// </summary>
    public static System.Collections.SortedList ListColumnsName;

    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="dgv"></param>
    /// <param name="filePath"></param>
    public static void ExportExcel(DataTable dtSource, string filePath)
    {
        if (ListColumnsName == null || ListColumnsName.Count == 0)
            throw (new Exception("请对ListColumnsName设置要导出的列明！"));
        HSSFWorkbook excelWorkbook = CreateExcelFile();
        InsertRow(dtSource, excelWorkbook);
        SaveExcelFile(excelWorkbook, filePath);
    }
    
    /// <summary>
    /// 导出Excel
    /// </summary>
    /// <param name="dgv"></param>
    /// <param name="filePath"></param>
    public static void ExportExcel(DataTable dtSource, Stream excelStream)
    {
        if (ListColumnsName == null || ListColumnsName.Count == 0)
            throw (new Exception("请对ListColumnsName设置要导出的列明！"));
        HSSFWorkbook excelWorkbook = CreateExcelFile();
        InsertRow(dtSource, excelWorkbook);
        SaveExcelFile(excelWorkbook, excelStream);
    }
    
    /// <summary>
    /// 保存Excel文件
    /// </summary>
    /// <param name="excelWorkBook"></param>
    /// <param name="filePath"></param>
    protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
    {
        FileStream file = null;
        try
        {
            file = new FileStream(filePath, FileMode.Create);
            excelWorkBook.Write(file);
        }
        finally
        {
            if (file != null)
            {
                file.Close();
            }
        }
    }
    
    /// <summary>
    /// 保存Excel文件
    /// </summary>
    /// <param name="excelWorkBook"></param>
    /// <param name="filePath"></param>
    protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
    {
        try
        {
            excelWorkBook.Write(excelStream);
        }
        finally
        {
        }
    }
    
    /// <summary>
    /// 创建Excel文件
    /// </summary>
    /// <param name="filePath"></param>
    protected static HSSFWorkbook CreateExcelFile()
    {
        HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        return hssfworkbook;
    }
    
    /// <summary>
    /// 创建excel表头
    /// </summary>
    /// <param name="dgv"></param>
    /// <param name="excelSheet"></param>
    protected static void CreateHeader(HSSFSheet excelSheet)
    {
        int cellIndex = 0;
        //循环导出列
        foreach (System.Collections.DictionaryEntry de in ListColumnsName)
        {
            HSSFRow newRow = excelSheet.CreateRow(0);
            HSSFCell newCell = newRow.CreateCell(cellIndex);
            newCell.SetCellValue(de.Value.ToString());
            cellIndex++;
        }
    }
    
    /// <summary>
    /// 插入数据行
    /// </summary>
    protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
    {
        int rowCount = 0;
        int sheetCount = 1;
        HSSFSheet newsheet = null;
        
        //循环数据源导出数据集
        newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
        CreateHeader(newsheet);
        foreach (DataRow dr in dtSource.Rows)
        {
            rowCount++;

            if (dr[5].ToString() == "1")
            {
                dr[15] = "男";
            }
            else
            {
                dr[15] = "女";
            }
            
            //超出10000条数据 创建新的工作簿
            if (rowCount == 50000)
            {
                rowCount = 1;
                sheetCount++;
                newsheet = excelWorkbook.CreateSheet("Sheet" + sheetCount);
                CreateHeader(newsheet);
            }
            
            HSSFRow newRow = newsheet.CreateRow(rowCount);
            InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook);
        }
    }
    
    /// <summary>
    /// 导出数据行
    /// </summary>
    /// <param name="dtSource"></param>
    /// <param name="drSource"></param>
    /// <param name="currentExcelRow"></param>
    /// <param name="excelSheet"></param>
    /// <param name="excelWorkBook"></param>
    protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook)
    {
        for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
        {
            //列名称
            string columnsName = (ListColumnsName.GetKey(cellIndex).ToString().Split('-'))[1];
            HSSFCell newCell = null;
            System.Type rowType = drSource[columnsName].GetType();
            string drValue = drSource[columnsName].ToString().Trim();
            switch (rowType.ToString())
            {
                case "System.String"://字符串类型
                    drValue = drValue.Replace("&", "&");
                    drValue = drValue.Replace(">", ">");
                    drValue = drValue.Replace("<", "<");
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(drValue);
                    break;
                case "System.DateTime"://日期类型
                    DateTime dateV;
                    DateTime.TryParse(drValue, out dateV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(dateV);

                    //格式化显示
                    HSSFCellStyle cellStyle = excelWorkBook.CreateCellStyle();
                    HSSFDataFormat format = excelWorkBook.CreateDataFormat();
                    cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
                    newCell.CellStyle = cellStyle;
                    break;
                case "System.Boolean"://布尔型
                    bool boolV = false;
                    bool.TryParse(drValue, out boolV);
                    newCell = currentExcelRow.CreateCell(cellIndex);

                    newCell.SetCellValue(boolV);
                    
                    break;
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(drValue, out intV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(intV.ToString());
                    break;
                case "System.Decimal"://浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(drValue, out doubV);
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue(doubV);
                    break;
                case "System.DBNull"://空值处理
                    newCell = currentExcelRow.CreateCell(cellIndex);
                    newCell.SetCellValue("");
                    break;
                default:
                    throw (new Exception(rowType.ToString() + "：类型数据无法处理!"));
            }
        }
    }
    #endregion
    
}
